Release 10.1A: OpenEdge Development:
Progress 4GL Handbook

FOR blocks

You’re already familiar with starting a block definition with the FOR keyword. You’ve seen the common FOR EACH table-name form, but there are a number of variations on the FOR statement. In contrast to the DO block, every FOR block provides all of the following services for you automatically:

Loops automatically through all the records that satisfy the record set definition in the block.
Reads the next record from the result set for you as it iterates.
Scopes those records to the block.
Scopes a frame to the block, and you can use the WITH FRAME phrase to specify that frame.
Provides database update services within a transaction.

The FOR statement defines the set of records you want the block to iterate through. Typically you use the EACH keyword to specify this set:

FOR EACH Customer WHERE State = "NH": DISPLAY CustNum Name. END.

When the block begins, Progress evaluates the expression and retrieves the first record that satisfies it. This record is scoped to the entire block. Each time the block iterates, Progress retrieves the next matching record and makes it available to the rest of the block. When the set of matching records is exhausted, Progress automatically terminates the block. You don’t have to add any checks or special syntax to exit the block at this point.

Sorting records by using the BY phrase

As you’ve seen, you can sort the records by using the BY phrase. The default is ascending order, but you cannot use the keyword ASCENDING to indicate this. You’ll get a syntax error, so just leave it out to get ascending order.

To sort in descending order, add the keyword DESCENDING to the BY phrase:

BY field [ DESCENDING ] . . .

To sort on multiple fields, you can repeat the BY phrase.

Joining tables using multiple FOR phrases

You can use multiple record phrases to join records from more than one table:

FOR EACH Customer WHERE State = "NH",     EACH Order OF Customer WHERE ShipDate NE ? :     DISPLAY Customer.Custnum Name OrderNum ShipDate. END.

Figure 6–8 shows the result.

Figure 6–8: Joining records from more than one table

There are several things to note about this example:

Progress retrieves and joins the tables in the order you specify them in, in effect following your instructions from left to right. In this example, it starts through the set of all Customers where the State field = “NH”. For the first record, it defines a set of Orders with the same CustNum value (represented by the OF syntax in this case). For each matching pair, it establishes that Customer record and its Order record and makes them available to all the rest of the statements in the block. Because there are typically multiple Orders for a Customer, the result is a one-to-many join, where the same Customer remains current for multiple iterations through the block, one for each of its Orders, as the output in Figure 6–8 shows.
If you change the sequence of the tables in the statement, Progress might retrieve a very different set of records. For example, using the syntax FOR EACH Order WHERE ShipDate NE ?, EACH Customer OF Order, you would get a list of every Order in the Order table with a ShipDate, plus its (one) matching Customer record. Because there’s just one Customer for each Order, this would result in a one-to-one join with no repeated records.
The default join you get is called an inner join. In matching up Customers to their Orders, Progress skips any Customer that has no Orders with a ShipDate because there is no matching pair of records. The alternative to this type of join, called an outer join, doesn’t skip those Customers with no Orders but instead supplies unknown values from a dummy Order when no Order satisfies the criteria. Progress has an OUTER-JOIN keyword, but you can use it only when you define queries of the kind you’ve seen in DEFINE QUERY statements, not in a FOR EACH block. To get the same effect using FOR EACH blocks, you can nest multiple blocks, one to retrieve and display the Customer and another to retrieve and display its Orders. You did this back in the sample procedure in Chapter 2, " Using Basic 4GL Constructs." Generally this is more effective than constructing a query that might involve a one-to-many relationship anyway, because it avoids having duplicated data from the first table in the join.
You can add a WHERE clause and/or a BY clause to each record phrase if you wish. You should always move each WHERE clause up as close to the front of the statement as possible to minimize the number of records retrieved. For example, the statement FOR EACH Customer, EACH Order OF Customer WHERE State = "NH" AND ShipDate NE ? would yield the same result but retrieve many more records in the process. It would go through the set of all Customers, retrieve each Order for each Customer, and then determine whether the State was “NH” and the ShipDate was not unknown. This code is very inefficient. The way the 4GL handles data retrieval is different from SQL, where the table selection is done at the beginning of a SELECT statement and the WHERE clause is after the list of tables. The SQL form depends on the presence of an optimizer that turns the statement into the most efficient retrieval possible. The advantage of the Progress form is that you have greater control over exactly how the data is retrieved. But with this control comes the responsibility to construct your FOR statements intelligently.
Because two records, Customer and Order, are scoped to the FOR block, you might need to qualify field names that appear in both of them. If you just write DISPLAY CustNum you get a syntax error when you try to run the procedure, as shown in Figure 6–9.



Figure 6–9: Syntax error message

Alternatives to the EACH keyword

Sometimes you just want a single record from a table. In that case, you can use the FIRST or LAST keyword in place of EACH, or possibly use no qualifier at all. For example, if you want to retrieve Orders and their Customers instead of the other way around, you can leave out the keyword EACH in the Customer phrase, because each Order has only one Customer:

FOR EACH Order WHERE ShipDate NE ?, Customer OF Order:     DISPLAY OrderNum ShipDate Name. END.

When you use this form, make sure that there is never more than one record satisfying the join. Otherwise, you get a run-time error telling you that there is no unique match.

If you’d like to see just the first Order for each Customer in New Hampshire, you can use the FIRST qualifier to accomplish that:

FOR EACH Customer WHERE State = "NH", FIRST Order OF Customer:     DISPLAY Customer.CustNum NAME OrderNum OrderDate. END.

Be careful, though. This form might not always yield the result you expect, because you have to consider just what is the first Order of a Customer? Progress uses an index of the Order table to traverse the rows.

Using indexes to relate and sort data

A database index allows the database manager to retrieve records quickly by looking up only the values of one or more key fields stored in separate database blocks from the records themselves, which then point to the location where the records are stored.

And what are the indexes of the Order table?

To get the answer to this question, take another look inside the Data Dictionary:

From the AppBuilder menu, select Tools Data Dictionary.
Select the Order table from the list of tables, then click the Indexes button:



Click the Index Properties button. The Index Properties dialog box appears and shows the properties of the first index, CustOrder:



This is the index Progress uses to retrieve the Orders, because its first component is the CustNum field, and that is the field it has to match against the CustNum from the Customer table. Since the other component in the index is the OrderNum field, this index sorts records by OrderNum within CustNum so your request for the FIRST Order returns the record with the lowest Order number.

Exit the Data Dictionary before you continue. Otherwise, Progress won’t let you run any procedures because it has a database transaction open and ready to save any changes you might make in the Data Dictionary.

Figure 6–10 shows the beginning of the display from the block FOR EACH Customer WHERE State = "NH", FIRST Order OF Customer.

Figure 6–10: Lowest Order number for each Customer

As expected, you see the Order with the lowest Order number for each Customer. If what you want is the earliest Order date, this output might not give you the information you are looking for.

Adding a BY phrase to the statement doesn’t help because Progress retrieves the records before applying the sort. So if you want the Order with the earliest Order date, it won’t work to do this:

FOR EACH Customer WHERE State = "NH", FIRST Order OF Customer BY OrderDate: DISPLAY Customer.CustNum NAME OrderNum OrderDate. END.

This code retrieves the same Orders as before, but then sorts the whole result set by the OrderDate field, as shown in Figure 6–11.

Figure 6–11: Orders sorted by OrderDate

Using the USE-INDEX phrase to force a retrieval order

If you look at all the indexes for the Order table in the Data Dictionary, you can see that there is also an index called OrderDate that uses the Order field. You can select the index to use when the default choice is not the one you want. Progress does this by adding a USE-INDEX phrase to the record phrase. This form of the FOR EACH statement is guaranteed to return the earliest OrderDate, even if it’s not the lowest OrderNum:

FOR EACH Customer WHERE State = "NH", FIRST Order OF Customer USE-INDEX OrderDate: DISPLAY Customer.CustNum NAME OrderNum OrderDate. END.

The result in Figure 6–12 shows that there is indeed an earlier Order for the first of your Customers that doesn’t have the lowest OrderNum.

Figure 6–12: Earliest Customer Order

Using the LEAVE statement to leave a block

Use the USE-INDEX phrase only when necessary. Progress is extremely effective at choosing the right index, or combination of multiple indexes, to optimize your data retrieval. In fact, there’s an alternative even in the present example that yields the same result without requiring you to know the names and fields in the Order table’s indexes. Take a look at this procedure:

FOR EACH Customer WHERE State = "NH" WITH FRAME f: DISPLAY Customer.CustNum Name. FOR EACH Order OF Customer BY OrderDate: DISPLAY OrderNum OrderDate WITH FRAME f. LEAVE. END. /* END FOR EACH Order */ END. /* END FOR EACH Customer */

This code uses nested blocks to retrieve the Customers and Orders separately. These nested blocks allow you to sort the Orders for a single Customer BY OrderDate. You have to define the set of all the Customer’s Orders using the FOR EACH phrase so that the BY phrase has the effect of sorting them by OrderDate. But you really only want to see the first one. To do this, you use another one-word 4GL statement: LEAVE. The LEAVE statement does exactly what you would expect it to: It leaves the block (specifically the innermost iterating block to the LEAVE statement) after displaying fields from the first of the Customer’s Orders. It does not execute any more statements that might be in the block nor does it loop through any more records that are in its result set. Instead, it moves back to the outer block to retrieve the next Customer.

Because the LEAVE statement looks for an iterating block to leave, it always leaves a FOR block. It leaves a DO block only if the DO statement has a qualifier, such as WHILE, that causes it to iterate. If there is no iterating block, Progress leaves the entire procedure.

Using block headers to identify blocks

If it isn’t clear what block the LEAVE statement applies to, or if you want it to apply to some other enclosing block, you can give a block a name followed by a colon and then specifically leave that block. This variant of the procedure has the same effect as the first one:

FOR EACH Customer WHERE State = "NH" WITH FRAME f: DISPLAY Customer.CustNum NAME. OrderBlock: FOR EACH Order OF Customer BY OrderDate: DISPLAY OrderNum OrderDate WITH FRAME f. LEAVE OrderBlock. END. /* END FOR EACH Order */ END. /* END FOR EACH Customer */

Just to see the effect of specifying a different block, you can try this variant:

CustBlock: FOR EACH Customer WHERE State = "NH" WITH FRAME f: DISPLAY Customer.CustNum NAME. OrderBlock: FOR EACH Order OF Customer BY OrderDate: DISPLAY OrderNum OrderDate WITH FRAME f. LEAVE CustBlock. END. /* END FOR EACH Order */ END. /* END FOR EACH Customer */

If you run this code, Progress leaves the outer FOR EACH Customer block after retrieving the first Order for the first Customer because of the change to the LEAVE statement, as shown in Figure 6–13.

Figure 6–13: Specifying a different block

Using NEXT, STOP, and QUIT to change block behavior

There’s another one-word statement that works much like LEAVE and that is NEXT. As you might expect, this statement skips any remaining statements in the block and proceeds to the next iteration of the block. You can qualify it with a block name the same way you do with LEAVE.

There are two more such statements that have increasingly more drastic consequences: STOP and QUIT.

STOP terminates the current procedure, backs out any active transactions, and returns to the Progress session’s startup procedure or to the Editor. You can intercept a STOP action by including the ON STOP phrase on a block header, which defines an action to take other than the default when the STOP condition occurs.

QUIT exits from Progress altogether in a run-time environment and returns to the operating system. If you’re running in a development environment, it has a similar effect to STOP and returns to the Editor or to the Desktop. There is also an ON QUIT phrase to intercept the QUIT condition in a block header and define an action to take other than quitting the session.

Qualifying a FOR statement with a frame reference

This most recent example also has an explicit frame reference in it:

FOR EACH Customer WHERE State = "NH" WITH FRAME f: DISPLAY Customer.CustNum Name. FOR EACH Order OF Customer BY OrderDate: DISPLAY OrderNum OrderDate WITH FRAME f. LEAVE. END. /* END FOR EACH Order */ END. /* END FOR EACH Customer */

Why is this necessary? A FOR EACH block scopes a frame to the block. By default, this is an unnamed frame. Without the specific frame reference, you get two nested frames, one for the Customer and one for its Orders. You saw this already in the sample procedure in Chapter 2, " Using Basic 4GL Constructs."

In this case, that isn’t what you want. Because there’s only one Order of interest for each Customer, you want to display all the fields together in the Customer frame. To get this effect, you have to override the default behavior and tell Progress to use the frame from the Customer block to display the Order fields. That is what these two references to WITH FRAME f do for you. Progress just keeps making room for new fields in the frame as it encounters them (unless you tell it exactly where to put each field, which is the norm in your GUI applications that use the AppBuilder to lay things out).